import mysql.connector
import sys
from PyQt5.QtWidgets import QApplication, QMainWindow, QWidget, QVBoxLayout, QHBoxLayout, QLabel, QLineEdit, QPushButton, QMessageBox
from PyQt5.QtGui import QIcon
from PyQt5.QtWidgets import QMainWindow, QTableWidget, QTableWidgetItem, QPushButton, QAbstractItemView
def get_db_connection():
    """
    获取数据库连接对象
    """
    try:
        db = mysql.connector.connect(
            host=" 26.227.45.9",
            user="zhuji",
            password="123456",
            database="baolong"
        )
        return db
    except mysql.connector.Error as e:
        print(f"连接数据库失败: {e}")
        return None
def delete_rootusers_table(host, user, password, database):
    """
    删除 rootusers 表
    
    Args:
        host (str): 数据库主机地址
        user (str): 数据库用户名
        password (str): 数据库密码
        database (str): 数据库名称
    """
    try:
        # 获取数据库连接
        db = mysql.connector.connect(
            host=host,
            user=user,
            password=password,
            database=database
        )
        
        # 创建游标
        cursor = db.cursor()
        
        # 执行删除表的 SQL 语句
        cursor.execute("DROP TABLE IF EXISTS rootusers")
        
        # 提交更改
        db.commit()
        
        print("rootusers 表已成功删除!")
    
    except mysql.connector.Error as e:
        # 发生错误时,回滚事务
        db.rollback()
        print(f"删除 rootusers 表失败: {e}")
    
    finally:
        # 关闭游标和连接
        cursor.close()
        db.close()
def get_data_from_rootusers_table():
    """
    从 rootusers 表中查询所有数据
    
    Returns:
        list: 查询结果列表
    """
    # 连接数据库
    db = get_db_connection()
    if db is None:
        return

    # 创建游标
    cursor = db.cursor()
    
    try:
        # 查询 rootusers 表中的所有数据
        cursor.execute("SELECT * FROM rootusers")
        rows = cursor.fetchall()
        print(rows)
        # 返回查询结果
        return rows
    except mysql.connector.Error as e:
        print(f"查询数据库失败: {e}")
        return None
    
    finally:
        # 关闭游标和连接
        cursor.close()
        db.close()

def delete_rootusers_table(host, user, password, database):
    """
    删除 rootusers 表
    
    Args:
        host (str): 数据库主机地址
        user (str): 数据库用户名
        password (str): 数据库密码
        database (str): 数据库名称
    """
    try:
        # 获取数据库连接
        db = mysql.connector.connect(
            host=host,
            user=user,
            password=password,
            database=database
        )
        
        # 创建游标
        cursor = db.cursor()
        
        # 执行删除表的 SQL 语句
        cursor.execute("DROP TABLE IF EXISTS rootusers")
        
        # 提交更改
        db.commit()
        
        print("rootusers 表已成功删除!")
    
    except mysql.connector.Error as e:
        # 发生错误时,回滚事务
        db.rollback()
        print(f"删除 rootusers 表失败: {e}")
    
    finally:
        # 关闭游标和连接
        cursor.close()
        db.close()

def create_users_table():
    try:
        # 获取数据库连接
        db = get_db_connection()
        if db is None:
            return

        # 创建游标
        cursor = db.cursor()
        # 检查 users 表是否存在
        cursor.execute("SHOW TABLES LIKE 'users'")
        result = cursor.fetchone()
        if not result:
            # 如果 users 表不存在,创建它
            cursor.execute("""
                CREATE TABLE users (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    username VARCHAR(255) UNIQUE NOT NULL,
                    password VARCHAR(255) NOT NULL
                )
            """)     
        # 关闭数据库连接
        cursor.close()
        db.close()
    except mysql.connector.Error as e:
        print()
def create_rootusers_table():
    try:
        # 获取数据库连接
        db = get_db_connection()
        if db is None:
            return

        # 创建游标
        cursor = db.cursor()
        # 检查 users 表是否存在
        cursor.execute("SHOW TABLES LIKE 'rootuses'")
        result = cursor.fetchone()
        if not result:
            # 如果 rootusers 表不存在,创建它
            cursor.execute("""
                CREATE TABLE rootusers (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    username VARCHAR(255) UNIQUE NOT NULL,
                    password VARCHAR(255) NOT NULL
                )
            """)
            cursor.execute("INSERT INTO rootusers (username, password) VALUES (%s, %s)", ("anqi", "250"))   
            db.commit()                     #提交这些更改,使其永久地应用到数据库
         
        # 关闭数据库连接
        cursor.close()
        db.close()
    except mysql.connector.Error as e:
        print()

def list_all_tables():
    """
    列出数据库中的所有表
    """
    db = get_db_connection()
    if db is None:
        return

    cursor = db.cursor()
    cursor.execute("SHOW TABLES")
    tables = cursor.fetchall()

    if tables:
        print("数据库中的所有表:")
        for table in tables:
            print(table[0])
    else:
        print("数据库中没有任何表")

    cursor.close()
    db.close()


class LoginWindow(QMainWindow):
    def __init__(self):
        super().__init__()
        self.initUI()

    def initUI(self):
        central_widget = QWidget()
        self.setCentralWidget(central_widget)

        # 创建用户名和密码输入框
        layout = QVBoxLayout()
        self.username_label = QLabel("用户名:")
        self.username_entry = QLineEdit()
        self.username_entry.setPlaceholderText("请输入用户名")
        self.password_label = QLabel("密码:")
        self.password_entry = QLineEdit()
        self.password_entry.setPlaceholderText("请输入密码")
        self.password_entry.setEchoMode(QLineEdit.Password)

        layout.addWidget(self.username_label)
        layout.addWidget(self.username_entry)
        layout.addWidget(self.password_label)
        layout.addWidget(self.password_entry)

        # 创建登录和注册按钮
        button_layout = QHBoxLayout()
        self.login_button = QPushButton("登录")
        self.login_button.setIcon(QIcon("login.png"))
        self.login_button.clicked.connect(self.login_clicked)
        self.register_button = QPushButton("注册")
        self.register_button.setIcon(QIcon("register.png"))
        self.register_button.clicked.connect(self.register_clicked)
        button_layout.addWidget(self.login_button)
        button_layout.addWidget(self.register_button)

        layout.addLayout(button_layout)
        central_widget.setLayout(layout)

        self.setWindowTitle("登录界面")
        self.setWindowIcon(QIcon("app_icon.png"))
        self.setFixedSize(450, 400)  # 设置窗口大小


    def login_clicked(self):
        username = self.username_entry.text()
        password = self.password_entry.text()

        # 检查输入框是否为空
        if not username:
            QMessageBox.warning(self, "警告", "用户名不能为空!")
            return
        if not password:
            QMessageBox.warning(self, "警告", "密码不能为空!")
            return

        try:
            # 获取数据库连接
            db = get_db_connection()
            if db is None:
                return

            cursor = db.cursor()

            # 检查用户是否存在于 rootusers 表
            cursor.execute("SELECT password FROM rootusers WHERE username = %s", (username,))
            result = cursor.fetchone()
            if result:
                # 管理员用户
                if result[0] == password:
                    # 密码正确, 进入管理员界面
                    self.admin_window = AdminWindow()
                    self.admin_window.show()
                    self.close()  # 关闭登录窗口
                    return
                else:
                    # 密码错误
                    QMessageBox.critical(self, "登录失败", "密码错误!")
                return

            # 检查用户是否存在于 users 表
            cursor.execute("SELECT password FROM users WHERE username = %s", (username,))
            result = cursor.fetchone()
            if result:
                # 普通用户
                if result[0] == password:
                    # 密码正确, 进入普通用户界面
                    QMessageBox.information(self, "登录成功", f"欢迎, {username}!")
                else:
                    # 密码错误
                    QMessageBox.critical(self, "登录失败", "密码错误!")
                return

            # 用户不存在
            QMessageBox.critical(self, "登录失败", "用户名不存在!")

        except mysql.connector.Error as err:
            QMessageBox.critical(self, "登录失败", f"数据库连接错误: {err}")
        finally:
            if 'cursor' in locals():
                cursor.close()
            if 'db' in locals():
                db.close()        

    def register_clicked(self):
            self.register_window = RegisterWindow()
            self.register_window.show()

        

   



class RegisterWindow(QMainWindow):
    def __init__(self):
        super().__init__()
        self.initUI()

    def initUI(self):
        central_widget = QWidget()
        self.setCentralWidget(central_widget)

        # 创建用户名、密码和确认密码输入框
        layout = QVBoxLayout()
        self.username_label = QLabel("用户名:")
        self.username_entry = QLineEdit()
        self.username_entry.setPlaceholderText("请输入用户名")
        self.password_label = QLabel("密码:")
        self.password_entry = QLineEdit()
        self.password_entry.setPlaceholderText("请输入密码")
        self.password_entry.setEchoMode(QLineEdit.Password)
        self.confirm_password_label = QLabel("确认密码:")
        self.confirm_password_entry = QLineEdit()
        self.confirm_password_entry.setPlaceholderText("请再次输入密码")
        self.confirm_password_entry.setEchoMode(QLineEdit.Password)

        layout.addWidget(self.username_label)
        layout.addWidget(self.username_entry)
        layout.addWidget(self.password_label)
        layout.addWidget(self.password_entry)
        layout.addWidget(self.confirm_password_label)
        layout.addWidget(self.confirm_password_entry)

        # 创建注册按钮
        self.register_button = QPushButton("注册")
        self.register_button.setIcon(QIcon("register.png"))
        self.register_button.clicked.connect(self.register_clicked)
        layout.addWidget(self.register_button)

        central_widget.setLayout(layout)

        self.setWindowTitle("注册界面")
        self.setWindowIcon(QIcon("app_icon.png"))
        self.setFixedSize(450, 450)  # 设置窗口大小
        self.center_window()  # 将窗口居中显示

    def register_clicked(self):
        username = self.username_entry.text()
        password = self.password_entry.text()
        confirm_password = self.confirm_password_entry.text()

        # 检查输入框是否为空
        if not username:
            QMessageBox.warning(self, "警告", "用户名不能为空!")
            return
        if not password:
            QMessageBox.warning(self, "警告", "密码不能为空!")
            return
        if not confirm_password:
            QMessageBox.warning(self, "警告", "确认密码不能为空!")
            return

        # 验证密码和确认密码是否相同
        if password == confirm_password:
            # 连接数据库并检查用户名是否已经存在
            db = get_db_connection()
            if db is None:
                return

            cursor = db.cursor()
            cursor.execute("""
                SELECT username 
                FROM (
                SELECT username 
                FROM users 
                WHERE username = %s
                UNION ALL
                SELECT username
                FROM rootusers
                WHERE username = %s
                ) AS combined_results
            """, (username, username))
            result = cursor.fetchone()
            if result:
                # 用户名已存在
                QMessageBox.critical(self, "错误", "用户名已被注册!")
                cursor.close()
                db.close()
                return
            else:
                # 将用户信息插入到数据库
                cursor.execute("INSERT INTO users (username, password) VALUES (%s, %s)", (username, password))
                db.commit()
                cursor.close()
                db.close()
                QMessageBox.information(self, "注册成功", f"注册成功! 用户名: {username}")
        else:
            # 密码和确认密码不一致
            QMessageBox.critical(self, "错误", "密码和确认密码不一致!")
    def center_window(self):
        """将窗口居中显示"""
        qr = self.frameGeometry()
        cp = self.window().geometry().center()
        qr.moveCenter(cp)
        self.move(qr.topLeft())



class AdminWindow(QMainWindow):
    def __init__(self):
        super().__init__()
        self.initUI()

    def initUI(self):
        self.setWindowTitle('管理员界面')
        self.setGeometry(100, 100, 800, 600)

        # 创建表格控件
        self.table = QTableWidget(self)
        self.table.setGeometry(50, 50, 700, 400)
        self.table.setColumnCount(3)
        self.table.setHorizontalHeaderLabels(['ID', '用户名', '权限'])
        self.table.setEditTriggers(QAbstractItemView.NoEditTriggers)
        self.table.setSelectionBehavior(QAbstractItemView.SelectRows)

        # 创建按钮
        self.add_btn = QPushButton('添加用户', self)
        self.add_btn.clicked.connect(self.add_user)
        self.add_btn.setGeometry(50, 480, 100, 50)

        self.edit_btn = QPushButton('编辑用户', self)
        self.edit_btn.clicked.connect(self.edit_user)
        self.edit_btn.setGeometry(350, 480, 100, 50)

        self.delete_btn = QPushButton('删除用户', self)
        self.delete_btn.clicked.connect(self.delete_user)
        self.delete_btn.setGeometry(650, 480, 100, 50)

        # 显示数据
        self.show_data()

    def show_data(self):
        # 获取数据库连接
        db = get_db_connection()
        if db is None:
            return

        try:
            # 从rootusers表中查询用户信息
            cursor = db.cursor()
            cursor.execute("SELECT id, username, role FROM rootusers")
            rows = cursor.fetchall()

            # 设置表格行数
            self.table.setRowCount(len(rows))

            # 将查询结果填充到表格中
            for i, row in enumerate(rows):
                self.table.setItem(i, 0, QTableWidgetItem(str(row[0])))
                self.table.setItem(i, 1, QTableWidgetItem(row[1]))
                self.table.setItem(i, 2, QTableWidgetItem(row[2]))

        except mysql.connector.Error as e:
            print(f"查询数据库失败: {e}")
        finally:
            # 关闭数据库连接
            db.close()

        # 添加固定的数据
        self.table.setRowCount(10)
        self.table.setItem(0, 0, QTableWidgetItem('1'))
        self.table.setItem(0, 1, QTableWidgetItem('admin'))
        self.table.setItem(0, 2, QTableWidgetItem('管理员'))

    

    def add_user(self):
        pass

    def edit_user(self):
        pass

    def delete_user(self):
        pass



if __name__ == "__main__":

    '''delete_rootusers_table(
    host="26.227.45.9",
    user="zhuji",
    password="123456",
    database="baolong"
)'''
    list_all_tables()
    #create_users_table()


    #create_rootusers_table()
    get_data_from_rootusers_table()
    app = QApplication(sys.argv)
    login_window = LoginWindow()
    login_window.show()                       #登录界面
    sys.exit(app.exec_())                     #Qt 应用程序
    